﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetRightInRoleToHandleClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetRightInRoleToHandleClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetRightInRoleToHandleClass]  
    @ClassID int,
    @UserName nvarchar(50),
    @Act_ID int,
    @AllowAccess bit output
/*

======================================================================
功能:    得到角色中@UserName是否能对@ClassID组进行@Act_ID操作
参数:
    @ClassID int             :    组（类）ID
    @UserName nvarchar(255)        :    用户名
    @Act_ID int            :    权利代号
    @Inherit int =1            :    是否继承

备注:    继承指是否继承在父组中的权利(在父组中有权利即在此组中有权利)
======================================================================

*/
AS
BEGIN
SET NOCOUNT ON;

SET @AllowAccess = 0;

IF EXISTS(
        SELECT 1 
            FROM 
                dbo.uds_role a,
                dbo.uds_staff_in_role b,
                dbo.uds_staff c,                
                dbo.UDS_Assign_Rule e
            WHERE
                a.role_id = b.role_id
                and c.staff_name = @username 
                and c.staff_id = b.staff_id 
                and e.based_on = 3 /* role */
                and e.role_id = a.role_id 
                and e.act_id = @act_id
                and e.team_id = 0 )
BEGIN
    SET @AllowAccess = 1;
    RETURN;
END

DECLARE @pteam TABLE (pid int not null);
with Class_ParentTree ( ClassID )
as
(
    select @ClassID

    union all    

    select c.ClassParentID
    from Class_ParentTree p inner join dbo.UDS_Class c on p.ClassID = c.ClassID and c.ClassID <> c.ClassParentID
)
insert into @pteam ( pid )
select ClassID
from Class_ParentTree;

--以下判断是判断用户是否再任何一个能访问此类的父类的角色中(能访问父类就能访问子类)

--得到所有父组中拥有此权利的角色ID
DECLARE @pAssignedRole TABLE (RoleID int not null);
INSERT INTO @pAssignedRole (RoleID)
    SELECT Role_ID 
        FROM 
            dbo.UDS_Assign_Rule a,
            dbo.uds_class b,
            @pteam c
        WHERE
            b.classid = a.team_id 
            and b.classid = c.pid
            And a.based_on = 3 /* role */
            And a.act_id = @Act_id

--用户是否在有权利的角色ID中?
IF EXISTS (
    SELECT 1
    FROM 
        dbo.uds_staff a,
        dbo.uds_staff_in_Role b,
        @pAssignedRole c
    WHERE
        a.staff_name = @username
        and a.staff_id = b.staff_id
        and b.Role_ID = c.RoleID
)
BEGIN
    SET @AllowAccess = 1;
END

END
GO